/*
 DNN Content Builder - sourceforge.net/projects/dnncb
 Copyright (c) 2004-2008
 by Emerson Lopes (emersonlopes@gmail.com)

 Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
 documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
 the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
 to permit persons to whom the Software is furnished to do so, subject to the following conditions:

 The above copyright notice and this permission notice shall be included in all copies or substantial portions 
 of the Software.

 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
 TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
 THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
 CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
 DEALINGS IN THE SOFTWARE.
*/

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Text;
using DotNetNuke;
using DotNetNuke.Common.Utilities;
using DNNContentBuilder.Configuration;

namespace DNNContentBuilder
{
	public sealed class CMS
	{
		#region Private Classes

		#region PartialDataFetcher
		/// <summary>
		/// TODO: document
		/// </summary>
		private class PartialDataFetcher
		{
			#region Member Variables
			private string tableName;
			private string orderBy;
			private string primaryKeyField;
			private DataTable universe;
			#endregion

			#region Constructors
			/// <summary>
			/// Initializes a new instance of the <see cref="PartialDataFetcher"/> class.
			/// </summary>
			/// <param name="tableName"></param>
			/// <param name="universe"></param>
			/// <param name="orderBy"></param>
			public PartialDataFetcher(string tableName, DataTable universe, string orderBy)
			{
				this.tableName = tableName;
				this.universe  = universe;
				this.orderBy   = orderBy;
			}
			#endregion

			#region Public Methods
			/// <summary>
			/// Fetches a specified number of rows from the <see cref="DataTable"/>
			/// </summary>
			/// <param name="rowCount"></param>
			/// <param name="onlyPublished"></param>
			/// <returns></returns>
			public DataTable Fetch(int rowCount, bool onlyPublished)
			{
				return this.Fetch(rowCount, onlyPublished ? " cbMetaData.Status='P' " : "");
			}

			/// <summary>
			/// Fetches a specified number of rows from the <see cref="DataTable"/> that
			/// conform to the specified criteria
			/// </summary>
			/// <param name="rowCount"></param>
			/// <param name="criteria"></param>
			/// <returns></returns>
			public DataTable Fetch(int rowCount, string criteria)
			{
				string cmdText = GetCommandText(criteria);
				string cachingKey = String.Format("{0}.{1}.{2}", this.GetType().FullName, cmdText, rowCount);

				CMSConfiguration settings = CMSConfiguration.GetConfig();

				if (!settings.EnableDataCache)
				{
					return this.FetchFromDataStore(rowCount, criteria);
				}

				object cachedResult = DataCache.GetCache(cachingKey);

				if (cachedResult == null)
				{
					cachedResult = this.FetchFromDataStore(rowCount, criteria);
					DataCache.SetCache(cachingKey, cachedResult, DateTime.Now.AddSeconds(settings.AbsoluteCacheExpiration));
				}

				return (DataTable)cachedResult;
			}
			#endregion

			#region Private Methods
			/// <summary>
			/// Fetch data physically from a data store (e.g., a database)
			/// </summary>
			/// <param name="rowCount"></param>
			/// <param name="criteria"></param>
			/// <returns></returns>
			private DataTable FetchFromDataStore(int rowCount, string criteria)
			{
				DataSet ds = new DataSet();

				string commandText = GetCommandText(criteria);

				using (DataConnectionController   dcController = new DataConnectionController())
				using (ConnectionStringController csController = new ConnectionStringController())
				{
					DataConnectionInfo   dcInfo = dcController.GetItemByTableName(tableName);
					ConnectionStringInfo csInfo = csController.GetItem(dcInfo.ConnectionStringId);
					
					using (OleDbDataAdapter adapter = new OleDbDataAdapter(commandText, csInfo.ConnectionString))
					{
						adapter.Fill(ds, 0, rowCount, this.tableName);
					}
				}

				return ds.Tables[0];
			}

			/// <summary>
			/// Get the command text required to select data according to the specified
			/// fetch criteria.
			/// </summary>
			/// <param name="criteria"></param>
			/// <returns></returns>
			private string GetCommandText(string criteria)
			{
				string where = this.BuildWhereClause(criteria);
				
				where = where + " AND ";
				
				where += String.Format(" cbMetadata.PKValue = {0} ",this.primaryKeyField);
				
				string order = this.orderBy != "" ? " ORDER BY " + this.orderBy : "";
					
				string commandText = String.Format(@"
					SELECT {0}.*
					FROM   {0}, cbMetaData
					       {1}
					       {2}", 
					this.tableName, where, order);

				return commandText;
			}

			/// <summary>
			/// Build a SQL WHERE clause.
			/// </summary>
			/// <param name="criteria"></param>
			/// <returns></returns>
			private string BuildWhereClause(string criteria)
			{
				string result = " WHERE ";
				
				StringBuilder inClause = new StringBuilder();

				using (DataConnectionController dcController = new DataConnectionController())
				{
					DataConnectionInfo dcInfo = dcController.GetItemByTableName(this.tableName);
					this.primaryKeyField = dcInfo.PrimaryKeyName.Remove(0,dcInfo.PrimaryKeyName.IndexOf(".") + 1);
				}

				string fullyQualifiedPrimaryKey = String.Format("{0}.{1}", this.tableName, this.primaryKeyField);


				foreach (DataRow dr in universe.Rows)
				{
					inClause.AppendFormat("'{0}',", dr[this.primaryKeyField].ToString());
				}

				if (inClause.Length > 0)
				{
					result += String.Format(" {0} IN ({1})", 
						fullyQualifiedPrimaryKey, inClause.ToString(0, inClause.Length - 1));
				}

				if (criteria != null && criteria.Length > 0)
				{
					if (result != " WHERE ") {
						result += " AND ";
					}
					result += String.Format(" {0}", criteria);
				}

				return result;
			}
			#endregion
		}
		#endregion

		#endregion
		
		private bool mDebugMode = false;
		
		private System.Web.UI.UserControl mControl;

		public CMS(System.Web.UI.UserControl control) {
			if (control == null) {
				throw new Exception("@@INVALID: USAGE: CMS cms = new CMS(this);");
			}
			
			mControl = control;
		}
		
		public bool DebugMode {
			get {
				return mDebugMode;
			}
			
			set {
				mDebugMode = value;
			}
		}
		
		private void DisplayDebugInfo(string title, string info) {
			if (DebugMode) {
				mControl.Response.Write("<table style=\"border-collapse: collapse; font-family: verdana; font-size: 11px;\" border=1>");
				mControl.Response.Write("<tr><td bgcolor=#ff0000><font color=#ffffff>");
				mControl.Response.Write(title);
				mControl.Response.Write("</td></tr><tr><td>");
				mControl.Response.Write(info);
				mControl.Response.Write("</td></tr></table><p>");
			}
		}

		private string NormalizeWhere(string where) {
			if (where != "") {
				where = where + " AND ";
			}
			
			return where;
		}
		
		private string NormalizeOrderBy(string orderBy) {
			orderBy = orderBy.ToUpper().Replace("ORDER BY", "");
			
			return orderBy == "" ? "" : " ORDER BY " + orderBy;
		}
		
		private Int32 NormalizeRowCount(Int32 rowCount) {
			if (rowCount == Null.NullInteger || rowCount < 1) {
				rowCount = 10;
			}
			
			return rowCount;
		}
		/// <summary>
		/// Retrieves content from the data store and returns a 
		/// <see cref="System.Data.DataTable"/> that you can iterate
		/// through or bind to ASP.NET databinding controls. This method currently supports only SQL Server
		/// </summary>
		/// <param name="tableName"></param>
		/// <param name="fields"></param>
		/// <param name="where"></param>
		/// <param name="orderBy"></param>
		/// <param name="rowCount"></param>
		/// <param name="lifeSpan"></param>
		/// <param name="userRoles"></param>
		/// <returns></returns>
		/// <remarks>Security is not checked by default</remarks>
		public DataTable SelectFrom(int portalId, string tableName, string fields, string where, string orderBy, int rowCount, Int32 lifeSpan, string userRoles) 
		{
			if (fields == "") {
				fields = "*";
			}
			
			try {
				where = NormalizeWhere(where);
				orderBy = NormalizeOrderBy(orderBy);
				rowCount = NormalizeRowCount(rowCount);

				if (tableName == String.Empty)
				{
					DisplayDebugInfo("SelectFrom:ERROR", "tableName?");
					return null;
				}

				//cachekey helps to prevent unnecessary db access by creating a unique entry in the cache for each query
				string cacheKey = String.Format("SelectFrom{0}{1}{2}{3}{4}{5}", tableName, fields, where, orderBy, rowCount,  userRoles);

				CMSConfiguration settings = CMSConfiguration.GetConfig();
				
				if (settings.EnableDataCache)
				{
					object cachedItem = DataCache.GetCache(cacheKey);
					if (cachedItem != null)
					{
						return (DataTable)cachedItem;
					}
				}
				
				string pkName  = "";
				string connectionString = "";

				object cachedPKName     = DataCache.GetCache(tableName + "pkName");
				object cachedConnString = DataCache.GetCache(tableName);

				if (cachedPKName != null)
				{
					pkName       = (string)cachedPKName;
					connectionString = (string)cachedConnString;
				} else {
					using (DataConnectionController   dcController = new DataConnectionController()) 
					using (ConnectionStringController csController = new ConnectionStringController()) {
						DataConnectionInfo   dcInfo = dcController.GetItemByTableName(tableName);
						
  						pkName = dcInfo.PrimaryKeyName;
  					
  						if (connectionString == "") {
	  						connectionString = csController.GetItem(dcInfo.ConnectionStringId).ConnectionString;
							DataCache.SetCache(tableName, connectionString);
  						}
					}
					DataCache.SetCache(tableName + "pkName", pkName);
				}
				
				//TODO: To find a way to identify the {objectQualifier} for cbMetaData table
				string commandText = String.Format(@"
                         SELECT {0} FROM {1}, cbMetaData WHERE ", fields, tableName);
			
				string[] roles = userRoles.Split(',');
				
				if (roles.Length == 0) {
					roles[0] = userRoles;
				} else {
					userRoles = "";
					for (int loop = 0; loop < roles.Length; loop++) {
						userRoles += String.Format("'{0}',", roles[loop].ToUpper());
					}
					userRoles = userRoles.Substring(0,userRoles.Length - 1);
				}
				
				commandText += where 
					+ String.Format(" cbMetaData.PKValue = {0} ", pkName) 
					+ @" AND cbMetaData.Status = 'P' 
                         AND cbMetaData.PortalID = " + portalId
                    + @" AND (     
                                   EXISTS(SELECT cbMetaData.MetaDataId FROM cbMetaData
					                      WHERE cbMetaData.MetaDataId IN (SELECT cbContentSecurity.MetadataId
                                          FROM   cbContentSecurity 
                                          WHERE  cbMetaData.MetaDataId = cbContentSecurity.MetaDataId 
                                          AND    UPPER(ContentAuthorizedViewer) IN (" + userRoles + @")))
							       OR  
		 			   			   NOT EXISTS (SELECT cbContentSecurity.MetadataId 
											   FROM   cbContentSecurity 
											   WHERE  cbMetaData.MetaDataId = cbContentSecurity.MetaDataId)
                             ) " + orderBy; 				
                       				
				DataSet ds = new DataSet();
				
				DisplayDebugInfo("CMS_DEBUG : SelectFromQuery", commandText);
				
				using (OleDbDataAdapter adapter = new OleDbDataAdapter(commandText, connectionString))
				{
						adapter.Fill(ds, 0, rowCount, tableName);
				}
	
				DataTable dt = ds.Tables[0];

				if (settings.EnableDataCache && lifeSpan > 0)
				{
					// Cache the item for lifeSpan seconds
					if (DataCache.GetCache(cacheKey) == null) {
						DataCache.SetCache(cacheKey, dt, DateTime.Now.AddSeconds(lifeSpan));
					}
				}
		
				return dt;
			}
			catch (Exception e) {
				string source = "DNN Content Builder (CMS:SelectFrom)";

				Commons.LogError(e, source);

				DisplayDebugInfo("SelectFrom:ERROR", e.Message);
				
				return null;
			}
		}

		/// <summary>
		/// Retrieves a <see cref="MetaDataInfo"/> instance based on the supplied 
		/// <see cref="Guid"/> value. In addition, the hit counter is automatically
		/// incremented so you can use the <see cref="GetMostPopularItems"/> method
		/// to retrieve a list of popular items.
		/// </summary>
		/// <param name="guid"></param>
		/// <param name="onlyPublished"></param>
		/// <param name="addHit"></param>
		/// <param name="userRoles"></param>
		/// <returns></returns>
		/// <remarks>
		/// If only published data is to be returned and the specified MetaData has not
		/// been published, this method will return a null <see cref="MetaDataInfo"/>.
		/// </remarks>
		public MetaDataInfo GetMetaData(string guid, bool onlyPublished, bool addHit, string userRoles) 
		{
			try {
				using (MetaDataController mdController = new MetaDataController())
				{
					DisplayDebugInfo("CMS_DEBUG : GetMetaData", "GUID=" + guid + ", addHit=" + addHit.ToString() + ", userRoles=" + userRoles);

					MetaDataInfo mdInfo = addHit ? mdController.GetItemAddingHit(guid, userRoles) : mdController.GetMetaDataByPKValue(guid, userRoles);
				
					if (mdInfo != null) {
						if (onlyPublished && mdInfo.Status != MetaDataStatus.Published)
						{
							return null;
						} else {
							return mdInfo;
						}
					} 

					DisplayDebugInfo("GetMetaData:ERROR", "Metadata(" + guid + ")?");
					
					return null;
				}
			}
			catch (Exception e) {
				string source =  "DNN Content Builder (CMS:GetMetaData)";

				Commons.LogError(e, source);

				return null;
			}
		}

		/// <summary>
		/// Retrieves an array of <see cref="MetaDataInfo"/> representing the most 
		/// popular items of the specified content type.
		/// </summary>
		/// <param name="formTitle"></param>
		/// <param name="rowCount"></param>
		/// <param name="userRoles"></param>
		/// <returns></returns>
		public ArrayList GetMostPopularItems(int portalId, string formTitle, int rowCount, bool onlyPublished, string userRoles) 
		{
			try {
				rowCount = NormalizeRowCount(rowCount);
				
				ArrayList result = new ArrayList();

				using (FormDefinitionController fdController = new FormDefinitionController())
				using (MetaDataController mdController = new MetaDataController())
				{
					DisplayDebugInfo("CMS_DEBUG : GetMostPopularItems", "portalId="+portalId.ToString() + ", formTitle=" + formTitle + ", rowCount=" + rowCount.ToString() + ", onlyPublished=" + onlyPublished.ToString() + ",userRoles=" + userRoles);

					FormDefinitionInfo fdInfo = fdController.GetFormByTitle(formTitle);
					ArrayList metadata = mdController.GetMostPopularItems(portalId, fdInfo.FormDefinitionId, rowCount, onlyPublished, userRoles);

					return metadata;
				}
			}
			catch (Exception e) {
				string source =  "DNN Content Builder (CMS:GetMostPopularItems)";

				Commons.LogError(e, source);

				return null;
			}
			
		}

		/// <summary>
		/// Trims a collection to maxCount items
		/// </summary>
		/// <param name="c"></param>
		/// <param name="maxCount"></param>
		/// <returns></returns>
		private ArrayList TrimCollection(ICollection c, int maxCount)
		{
			ArrayList result = new ArrayList(c);

			if (result.Count > maxCount)
			{
				result.RemoveRange(0, maxCount - result.Count);
			}

			return result;
		}

		public int GetFormDefinitionId(string formTitle) {
			
			using (FormDefinitionController fdController = new FormDefinitionController()) {
				FormDefinitionInfo fdInfo = fdController.GetFormByTitle(formTitle);
				return fdInfo.FormDefinitionId;
			}
		}

		/// <summary>
		/// Direct SQL execution. Be carefull using this command.
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="sql"></param>
		/// <returns></returns>

		public OleDbDataReader ExecSQL (OleDbConnection conn, string sql) {
			OleDbCommand command = conn.CreateCommand();

			try
			{
				command.CommandText = sql;

				DisplayDebugInfo("CMS_DEBUG : ExecSQL", sql);

				return command.ExecuteReader();
			}
			catch (Exception e) {
				string source =  "DNN Content Builder (CMS:ExecSQL)";

				Commons.LogError(e, source);
			
				return null;
			}
		}

		/// <summary>
		/// Retrieves an array of <see cref="MetaDataInfo"/> representing content that matches
		/// sibling taxonomy and form title.
		/// </summary>
		/// <param name="taxonomyTitle"></param>
		/// <param name="onlyPublished"></param>
		/// <returns></returns>
		public ArrayList GetXRef(Int32 portalId, string formTitle, string taxonomyPath, bool onlyPublished, int rowCount, string where, string orderBy, string userRoles) 
		{
			DisplayDebugInfo("CMS_DEBUG : GetXRef", "portalId=" + portalId.ToString() + ", formTitle=" + formTitle +", taxonomyPath=" + taxonomyPath + ", onlyPublished=" + onlyPublished.ToString() + ", rowCount=" + rowCount.ToString() + ", where=" + where + ", orderBy=" + orderBy + ", userRoles=" + userRoles);
			
			where = NormalizeWhere(where);
			orderBy = NormalizeOrderBy(orderBy);
			rowCount = NormalizeRowCount(rowCount);
				
			if (taxonomyPath == "") {
				DisplayDebugInfo("GetXRef:ERROR", "taxonomyPath?");
				return null;
			}
			
			string[] tokens = taxonomyPath.Split('/');
			
			if (tokens.Length == 0 || tokens[0].IndexOf("/",0) != 0) {
				string source =  "DNN Content Builder (CMS:ExecSQL)";

				Commons.LogError(new ApplicationException("Taxonomy=\"\""), source);
				
				DisplayDebugInfo("GetXRef:ERROR",  "taxonomy(" + tokens.ToString() + "?");
				
				return null;
			}
			
			int parent = -1;
	
			StringBuilder path = new StringBuilder();
			
			using (TaxonomyController taxonomyController = new TaxonomyController())
			{
				for (int loop = 0; loop < tokens.Length - 1;loop++) {
					TaxonomyInfo taxonomyInfo = taxonomyController.GetByTitle(tokens[loop], parent);
					
					path.Append("/");
					
					if (taxonomyInfo == null) {
						tokens[loop] = String.Format("<b>{0}</b>", tokens[loop]);
						
						path.Append(tokens[loop]);
						
						string source =  "DNN Content Builder (CMS:GetXRef)";
						
						Commons.LogError(new ApplicationException(tokens[loop] + "?"), source);
						
						DisplayDebugInfo("GetXRef:ERROR", tokens[loop] + "?");

						return null;
					}

					path.Append(tokens[loop]);
					
					parent = taxonomyInfo.ParentId;
				}
			}

			try {
				using (MetaDataController mdController = new MetaDataController()) {
					ArrayList metadata = mdController.XRefByTaxonomy(portalId, GetFormDefinitionId(formTitle), parent, rowCount, where, orderBy, userRoles);
					return metadata;
				}
			}
			catch (Exception e) {
				string source =  "DNN Content Builder (CMS:GetXRef)";

				Commons.LogError(e, source);

				return null;
			}
		}

//		/// <summary>
//		/// Retrieves an array of <see cref="MetaDataInfo"/> representing content that matches
//		/// the supplied token. Token is a single word present in the metadata title.
//		/// </summary>
//		/// <param name="taxonomyTitle"></param>
//		/// <param name="token"></param>
//		/// <param name="onlyPublished"></param>
//		/// <returns></returns>
//		public ArrayList GetXRefBasedOnToken(Int32 portalId, string formTitle, string taxonomyTitle, string token, bool onlyPublished, int rowCount, string where, string orderBy, string userRoles) 
//		{
//			try {
//				ArrayList result = new ArrayList();
//				ArrayList metadata = CMS.GetXRef(portalId, formTitle, taxonomyTitle, onlyPublished, rowCount, where, orderBy, userRoles.ToUpper());
//
//				where = NormalizeWhere(where);
//				orderBy = NormalizeOrderBy(orderBy);
//				rowCount = NormalizeRowCount(rowCount);
//				
//				foreach (MetaDataInfo item in metadata)
//				{
//					if (item.Title.IndexOf(token) != -1)
//					{
//						result.Add(item);
//					}
//				}
//
//				return result;
//			}
//			catch (Exception e) {
//				Commons.LogError("CMS Error: GetXRefBasedOnToken", e.Message, "An error has occurred during token recovery.", "DNN Content Builder");
//
//				return null;
//			}
//		}

		/// <summary>
		/// This method retrieves a <see cref="System.Data.OleDb.OleDbConnection"/> that
		/// you can use to retrieve content from your content database.
		/// </summary>
		/// <param name="connectionStringTitle"></param>
		/// <returns></returns>
		public OleDbConnection GetConnection(string connectionStringTitle) 
		{
			try {
				using (ConnectionStringController csController = new ConnectionStringController())
				{
					DisplayDebugInfo("CMS_DEBUG : GetConnection", connectionStringTitle);
					
					ConnectionStringInfo csInfo = csController.GetItemByTitle(connectionStringTitle);

					return new OleDbConnection(csInfo.ConnectionString);
				}
			}
			catch (Exception e) {
				string source =  "DNN Content Builder (CMS:GetConnection)";

				Commons.LogError(e, source);

				return null;
			}
		}
		
		public Int32 GetTaxonomyId(string path) {
			try {
					DisplayDebugInfo("CMS_DEBUG : GetTaxonomyId", path);

				if (path == "" || path.Substring(0,1) != "/") {
					DisplayDebugInfo("GetTaxonomyId:ERROR", "path?");
					
					return Null.NullInteger;
				}
				
				string[] tokens = path.Split('/');
				
				int parentId = -1;
				
				if (tokens.Length > 0) {
					for (int loop = 0; loop < tokens.Length; loop++) {
						TaxonomyController txController = new TaxonomyController();
						TaxonomyInfo txInfo = txController.GetByTitle(tokens[loop].ToUpper(), parentId);
						if (txInfo != null) {
							parentId = txInfo.TaxonomyId;
						} else {
							DisplayDebugInfo("GetTaxonomyId:ERROR", tokens[loop] + "?");

							return Null.NullInteger;
						}
					}
				}
				
				return parentId;
			}
			catch (Exception e) {
				string source =  "DNN Content Builder (CMS:GetTaxonomyId)";

				Commons.LogError(e, source);

				return Null.NullInteger;
			}
			
		}
	}
}
